package com.admin.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.MobileProductBean;

@Repository
public class MobileProductDao {

	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;
	
	/**
	 * 根据手机机型ID查询其绑定的产品
	 * @param mobileTypeId
	 * @param start
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public List<MobileProductBean> findByMobileTypeId(int mobileTypeId,int start,int pageSize) throws SQLException{
		final String sql = "select mp.id,mp.mobileTypeId,p.productId,p.productName from product p  left join mobile_product mp on mobileTypeId = ?" +
				" and mp.productId=p.productId order by id desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<MobileProductBean>(MobileProductBean.class), new Object[]{mobileTypeId,start,pageSize});
	}
	
	/**
	 * 根據手機ID查詢其第一個綁定的產品
	 * @param mobileTypeId
	 * @return
	 * @throws SQLException
	 */
	public MobileProductBean findFirstByMobileTypeId(int mobileTypeId) throws SQLException{
		String sql = "select * from mobile_product where mobileTypeId = ? order by id asc";
		List<MobileProductBean> mpList = queryRunner.query(sql, new BeanListHandler<MobileProductBean>(MobileProductBean.class), mobileTypeId);
		if(mpList!=null&&mpList.size()>0){
			return mpList.get(0);
		}else{
			return null;
		}
	}
	
	/**
	 * 根据手机统计绑定的产品数量
	 * @param mobileTypeId
	 * @return
	 */
	public int findCountByMobileTypeId(int mobileTypeId){
		final String sql = "select count(0) from mobile_product where mobileTypeId = ?";
		return jdbcTemplate.queryForInt(sql,mobileTypeId);
	}
	
	/**
	 * 批量添加产品
	 * @param mobileTypeId
	 * @param productIds
	 * @return
	 */
	public int[] insertBatch(final int mobileTypeId,final String[] productIds){
		final String sql = "insert into mobile_product(mobileTypeId,productId)values(?,?)";
		final int size = productIds.length;
		return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, mobileTypeId);
				ps.setInt(2, Integer.valueOf(productIds[i]));
			}
			
			@Override
			public int getBatchSize() {
				return size;
			}
		});
	}
	
	/**
	 * 批量删除
	 * @param ids
	 * @return
	 */
	public int[] delBatch(final String[] ids){
		final String sql = "delete from mobile_product where id = ?";
		final int size = ids.length;
		return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, Integer.valueOf(ids[i]));
			}
			
			@Override
			public int getBatchSize() {
				return size;
			}
		});
	}
	
	/**
	 * 根据产品ID删除机型与产品的关系
	 * @param productId
	 * @return
	 */
	public int delByProductId(int productId){
		final String sql = "delete from mobile_product where productId = ?";
		return jdbcTemplate.update(sql, productId);
	}
	
	/**
	 * 根据机型ID删除机型与产品的关系
	 * @param productId
	 * @return
	 */
	public int delBymobileTypeId(int mobileTypeId){
		final String sql = "delete from mobile_product where mobileTypeId = ?";
		return jdbcTemplate.update(sql, mobileTypeId);
	}
}
